/*
Name:digger_sourceData
Author: Terry Dong
Description: data access layer
*/
using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Reflection;
using Gammar.Digger.SystemFramework;
using Gammar.Digger.Entity;
using Gammar.Digger.IDAL;
using Gammar.Digger.Data;
namespace Gammar.Digger.DALSqlServer
{
	public partial class digger_sourceData:Idigger_sourceData
	{
		private DbSqlServer helper;
		
		#region constructors
		/// <summary>
        /// default constructor with the defaultConn
        /// </summary>
        public digger_sourceData()
        {
			helper = new DbSqlServer(ConnectionString.SqlServer_GPriceCollect);
        }
		
		/// <summary>
        /// default constructor with the defaultConn
        /// </summary>
		public digger_sourceData(string connString)
		{
			helper = new DbSqlServer(connString);	
		}
		#endregion
		
		#region IDataBase<digger_sourceData> 

        public int Add(digger_sourceDataEntity entity)
        {
           	StringBuilder strSql=new StringBuilder();
			strSql.Append("INSERT INTO digger_sourceData(");
			strSql.Append("title,detail,original_price,sale_price,image,create_time,source_name,image_path,last_modify_time,brand,url_id)");
			strSql.Append(" VALUES (");
			strSql.Append("@title,@detail,@original_price,@sale_price,@image,@create_time,@source_name,@image_path,@last_modify_time,@brand,@url_id)");
			SqlParameter[] parameters = {
					new SqlParameter("@title",SqlDbType.VarChar),
					new SqlParameter("@detail",SqlDbType.Text),
					new SqlParameter("@original_price",SqlDbType.VarChar),
					new SqlParameter("@sale_price",SqlDbType.VarChar),
					new SqlParameter("@image",SqlDbType.VarChar),
					new SqlParameter("@create_time",SqlDbType.VarChar),
					new SqlParameter("@source_name",SqlDbType.VarChar),
					new SqlParameter("@image_path",SqlDbType.VarChar),
					new SqlParameter("@last_modify_time",SqlDbType.BigInt),
					new SqlParameter("@brand",SqlDbType.VarChar),
					new SqlParameter("@url_id",SqlDbType.UniqueIdentifier)
					};
				parameters[0].Value = entity.title;
				parameters[1].Value = entity.detail;
				parameters[2].Value = entity.original_price;
				parameters[3].Value = entity.sale_price;
				parameters[4].Value = entity.image;
				parameters[5].Value = entity.create_time;
				parameters[6].Value = entity.source_name;
				parameters[7].Value = entity.image_path;
				parameters[8].Value = entity.last_modify_time;
				parameters[9].Value = entity.brand;
				parameters[10].Value = entity.url_id;
				
			return helper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
		
        public int Update(digger_sourceDataEntity entity)
        {
			
           StringBuilder strSql=new StringBuilder();
			strSql.Append("UPDATE digger_sourceData SET ");
				strSql.Append("title=@title,");
				strSql.Append("detail=@detail,");
				strSql.Append("original_price=@original_price,");
				strSql.Append("sale_price=@sale_price,");
				strSql.Append("image=@image,");
				strSql.Append("create_time=@create_time,");
				strSql.Append("source_name=@source_name,");
				strSql.Append("image_path=@image_path,");
				strSql.Append("last_modify_time=@last_modify_time,");
				strSql.Append("brand=@brand,");
				strSql.Append("url_id=@url_id");
		
			strSql.Append(" WHERE data_id=@data_id");
			SqlParameter[] parameters = {
					new SqlParameter("@data_id",SqlDbType.UniqueIdentifier),
					new SqlParameter("@title",SqlDbType.VarChar),
					new SqlParameter("@detail",SqlDbType.Text),
					new SqlParameter("@original_price",SqlDbType.VarChar),
					new SqlParameter("@sale_price",SqlDbType.VarChar),
					new SqlParameter("@image",SqlDbType.VarChar),
					new SqlParameter("@create_time",SqlDbType.VarChar),
					new SqlParameter("@source_name",SqlDbType.VarChar),
					new SqlParameter("@image_path",SqlDbType.VarChar),
					new SqlParameter("@last_modify_time",SqlDbType.BigInt),
					new SqlParameter("@brand",SqlDbType.VarChar),
					new SqlParameter("@url_id",SqlDbType.UniqueIdentifier)
					};
				parameters[0].Value = entity.data_id;
				parameters[1].Value = entity.title;
				parameters[2].Value = entity.detail;
				parameters[3].Value = entity.original_price;
				parameters[4].Value = entity.sale_price;
				parameters[5].Value = entity.image;
				parameters[6].Value = entity.create_time;
				parameters[7].Value = entity.source_name;
				parameters[8].Value = entity.image_path;
				parameters[9].Value = entity.last_modify_time;
				parameters[10].Value = entity.brand;
				parameters[11].Value = entity.url_id;
				
			return helper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
		
		public int UpdateBydata_id(digger_sourceDataEntity entity)
        { 
            string where=string.Format("data_id='{0}'",entity.data_id);
			entity.data_id=null;
        	return Updatepartial(entity, where);
		}

        public int Delete(string strWhere)
        {
           StringBuilder strSql=new StringBuilder();
			strSql.Append("DELETE FROM digger_sourceData ");
			if(!string.IsNullOrEmpty(strWhere))
			{
				strSql.Append(" WHERE "+strWhere);
			}
			return helper.ExecuteNonQuery(strSql.ToString());
        }

        public digger_sourceDataEntity GetEntity(int primaryKeyId)
        {
            StringBuilder strSql=new StringBuilder();
			strSql.Append("select * from digger_sourceData ");
			strSql.Append(" where data_id=@primaryKeyId");
			SqlParameter[] parameters = {
					new SqlParameter("@primaryKeyId", SqlDbType.Int)};
			parameters[0].Value = primaryKeyId;
			SqlDataReader dr = helper.ExecuteReader(strSql.ToString(), parameters) as SqlDataReader;
            if (dr.HasRows)
            {
                dr.Read();
				digger_sourceDataEntity entity=new digger_sourceDataEntity();
				if(!Convert.IsDBNull(dr["data_id"]))
                {
                    entity.data_id = (Guid)dr["data_id"];
                }
				entity.title=dr["title"].ToString();		
				entity.detail=dr["detail"].ToString();		
				entity.original_price=dr["original_price"].ToString();		
				entity.sale_price=dr["sale_price"].ToString();		
				entity.image=dr["image"].ToString();		
				entity.create_time=dr["create_time"].ToString();		
				entity.source_name=dr["source_name"].ToString();		
				entity.image_path=dr["image_path"].ToString();		
				if(!Convert.IsDBNull(dr["last_modify_time"]))
                {
                    entity.last_modify_time = (long)dr["last_modify_time"];
                }
				entity.brand=dr["brand"].ToString();		
				if(!Convert.IsDBNull(dr["url_id"]))
                {
                    entity.url_id = (Guid)dr["url_id"];
                }
				dr.Close();
            	dr.Dispose();
				return entity;
			}
			else
			{
				dr.Close();
            	dr.Dispose();
				return null;
			}
        }

        public object GetCell(string cellName, string where)
        {
            string strSql = string.Empty;
            if (string.IsNullOrEmpty(where))
            {
                strSql = string.Format("SELECT {0} FROM digger_sourceData", cellName);
            }
            else
            {
                strSql = string.Format("SELECT {0} FROM digger_sourceData Where {1}", cellName, where);
            }
            return helper.ExecuteCell(strSql);
        }

		public DataTable GetTable(string strWhere)
		{
           StringBuilder strSql=new StringBuilder();
			strSql.Append("SELECT data_id,title,detail,original_price,sale_price,image,create_time,source_name,image_path,last_modify_time,brand,url_id");
			strSql.Append(" FROM digger_sourceData ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" WHERE "+strWhere);
			}
			return helper.ExecuteAdapter(strSql.ToString());
		}
		
        public SortList<digger_sourceDataEntity> GetList(string strWhere)
        {
            StringBuilder strSql=new StringBuilder();
			strSql.Append("select data_id,title,detail,original_price,sale_price,image,create_time,source_name,image_path,last_modify_time,brand,url_id");
			strSql.Append(" FROM digger_sourceData ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			SqlDataReader dr = helper.ExecuteReader(strSql.ToString()) as SqlDataReader;
			SortList<digger_sourceDataEntity> list= new SortList<digger_sourceDataEntity>();
			while (dr.Read())
            {
				digger_sourceDataEntity entity=new digger_sourceDataEntity();
				if(!Convert.IsDBNull(dr["data_id"]))
                {
                    entity.data_id = (Guid)dr["data_id"];
                }
				entity.title=dr["title"].ToString();
				entity.detail=dr["detail"].ToString();
				entity.original_price=dr["original_price"].ToString();
				entity.sale_price=dr["sale_price"].ToString();
				entity.image=dr["image"].ToString();
				entity.create_time=dr["create_time"].ToString();
				entity.source_name=dr["source_name"].ToString();
				entity.image_path=dr["image_path"].ToString();
				if(!Convert.IsDBNull(dr["last_modify_time"]))
                {
                    entity.last_modify_time = (long)dr["last_modify_time"];
                }
				entity.brand=dr["brand"].ToString();
				if(!Convert.IsDBNull(dr["url_id"]))
                {
                    entity.url_id = (Guid)dr["url_id"];
                }
                list.Add(entity);
            }

            dr.Close();
            dr.Dispose();

            return list;
        }
		
				
		#region select,add,update the fields that you want to operate.
		
		public int AddPartial(digger_sourceDataEntity entity)
        {
            int reval = 0;
            string sql = string.Empty;
            SqlParameter[] param = DataUtility.BuildAdd(entity, "digger_sourceData", ref sql);

            if (param!=null)
            {
                reval = helper.ExecuteNonQuery(sql,param);
            }
           return reval;            
            
        }
		
		public int Updatepartial(digger_sourceDataEntity entity, string where)
		{
            int reval = 0;
            string sql = string.Empty;
            SqlParameter[] param = DataUtility.BuildUpdate(entity, "digger_sourceData",where, ref sql);

            if (param != null)
            {
                reval = helper.ExecuteNonQuery(sql,param);
            }
            return reval;     
		}
		
		public List<digger_sourceDataEntity> GetPartial(string[] selectedFields,string where)
        {
            List<digger_sourceDataEntity> list = new List<digger_sourceDataEntity>();
            string sql = DataUtility.BuildSelect(selectedFields, "digger_sourceData", where);
            if (!string.IsNullOrEmpty(sql))
            {
                DbDataReader reader= helper.ExecuteReader(sql);
             
                while (reader.Read())
                {
                    digger_sourceDataEntity model = new digger_sourceDataEntity();
                    foreach (string field in selectedFields)
                    {
                        PropertyInfo propInfo = model.GetType().GetProperty(field);
                        propInfo.SetValue(model, reader[field], null);
                    }

                    list.Add(model);
                }

                reader.Close();
            }
            return list;
        }
		#endregion
		

        #endregion
	}
}
